<?php
/**
 * Created by PhpStorm.
 * User: 34673
 * Date: 2020/5/12
 * Time: 9:44
 */

namespace app\cms\controller;

use think\admin\Controller;
use PhpOffice\PhpSpreadsheet;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use think\admin\service\AdminService;

/**
 * CMS模块的基类
 * Class BaseController
 * @package app\cms\controller
 */
class BaseController extends Controller
{

    /*public $work = array(
        "燃气经营企业主要负责人" => array("负责人"),
        "燃气经营企业安全生产管理人员" => array("管理人员"),
        "管道燃气经营企业运行、维修和抢修人员" => array("运行工","巡线工","维修抢修工","电工"),
        "瓶装燃气经营企业运行、维修和抢修人员" => array("运行工","巡线工","维修抢修工","电工"),
        "燃气汽车加气经营企业运行、维修和抢修人员" => array("运行工","巡线工","维修抢修工","电工"),
    );*/

    /**
     * 输入文件名及每列所对应的字段名，即可进行导入
     * @param $inputFileName
     * @param $fields  [ "month", "type", "qty" ]
     * @param int $startRow
     * @throws PhpSpreadsheet\Reader\Exception
     */
    public function importExcel($inputFileName, $fields, $startRow=2, $fixed){

        $reader = PhpSpreadsheet\IOFactory::createReader('Xlsx');
        $reader->setReadDataOnly(TRUE);
        $spreadsheet = $reader->load($inputFileName); //载入excel表格

        $worksheet = $spreadsheet->getActiveSheet();
        $highestRow = $worksheet->getHighestRow(); // 总行数

        $lines = $highestRow - 1;
        if ($lines <= 0) {
            return array("status"=>0, "info"=>"表格中没有数据");
        }

        for ($row = $startRow; $row <= $highestRow; ++$row) {
            $data = array_merge($fixed, array(
                    'create_by' => AdminService::instance()->getUserId(),
                    'update_at' => time(),
                    'create_at' => time(),
                )
            );
            for($i=0; $i<count($fields); $i++){
                $data[$fields[$i]] = $worksheet->getCellByColumnAndRow($i+1, $row)->getValue();
            }
            $this->app->db->name($this->table)->save($data);
        }
        return array("status"=>1, "info"=>"导入成功");
    }

    public function getImportDataFromExcel($inputFileName, $fields, $startRow=2){
        $reader = PhpSpreadsheet\IOFactory::createReader('Xlsx');
        $reader->setReadDataOnly(TRUE);
        $spreadsheet = $reader->load($inputFileName); //载入excel表格

        $worksheet = $spreadsheet->getActiveSheet();
        $highestRow = $worksheet->getHighestRow(); // 总行数

        $result = array();
        $lines = $highestRow - 1;
        if ($lines <= 0) {
            return $result;
        }

        for ($row = $startRow; $row <= $highestRow; ++$row) {
            $data = array('update_at' => time());
            for($i=0; $i<count($fields); $i++){
                $data[$fields[$i]] = $worksheet->getCellByColumnAndRow($i+1, $row)->getValue();
            }
            $result[] = $data;
        }
        return $result;
    }

    /**
     * 清除文件路径中的主机地址
     * @param $inputFileName
     * @return string
     */
    public function filerFilePath($inputFileName){
        $inputFileName = str_replace('https://','',$inputFileName);
        $inputFileName = str_replace('http://','',$inputFileName);
        $inputFileName = str_replace($_SERVER['HTTP_HOST'],'',$inputFileName);
        return $_SERVER['DOCUMENT_ROOT'].$inputFileName;
    }

    /**
     * 传入title和数据即可导出EXCEL文件
     * @param $title  [array("month","月份"), array("type","用户类型"), array("qty","需求量")];
     * @param $data   [ array("month"=>"202002"),"type"=>"1","qty"=>1000) ]
     * @throws PhpSpreadsheet\Writer\Exception
     */
    public function exportExcel($title, $data){

        // Create new Spreadsheet object
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // 方法一，使用 setCellValueByColumnAndRow
        foreach ($title as $key => $value) {
            $sheet->setCellValueByColumnAndRow($key + 1, 1, $value[1]);
        }
        $row = 2; // 从第二行开始
        for($i=0; $i<count($data); $i++){
            foreach ($title as $key => $value) {
                $sheet->setCellValueByColumnAndRow($key + 1, $row, $data[$i][$value[0]]);
            }
            $row += 1;
        }

        // Redirect output to a client’s web browser (Xlsx)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.time().'.xlsx"');
        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');

        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0

        $writer = PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }



}